﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.Objects;
using System.Data.SqlClient;
using System.Linq;
using System.Web;

namespace JamesThew.Dashboard
{
    public class CategoryDB
    {

        public List<Category> Get()
        {
            List<Category> lc = new List<Category>();

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SELECT * FROM CMS_Categories WHERE 1=1";
            SqlDataReader rd;

            rd = cmd.ExecuteReader();
            DataTable dt = new DataTable();

            dt.Load(rd);

            for (int i = 0; i < dt.Rows.Count; i++)
            {

                int CategoryID = (int)dt.Rows[i][0];
                String CategoryName = dt.Rows[i][1].ToString();
                String CategoryDescription = dt.Rows[i][2].ToString();
                int CategoryParent = (int) dt.Rows[i][3];
                int PostCount = (int)dt.Rows[i][4];

                Category cat = new Category(CategoryID, CategoryName, CategoryDescription, CategoryParent, PostCount);
                lc.Add(cat);
            }

            con.Close();

            return lc;
        }

        public Boolean Delete(int CategoryID)
        {

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand("DeleteCategory", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@CategoryID", CategoryID));

            Int32 check = cmd.ExecuteNonQuery();
            con.Close();
            if (check > 0) return true;
            return false;
        }

        public Boolean Add(Category cat)
        {

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand("InsertCategory", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@CategoryName", cat.CategoryName));
            cmd.Parameters.Add(new SqlParameter("@CategoryDescription", cat.CategoryDescription));
            cmd.Parameters.Add(new SqlParameter("@CategoryParent", cat.CategoryParent));


            cmd.Parameters.Add(new SqlParameter("@CategoryID", SqlDbType.Int, 6));
            cmd.Parameters["@CategoryID"].Direction = ParameterDirection.Output;

            Int32 check = cmd.ExecuteNonQuery();
            string khoa = cmd.Parameters["@CategoryID"].Value.ToString();
            Int32 CategoryID = JamesThew.Includes.Core.ParseInt(khoa);

            cat.CategoryID = CategoryID;

            con.Close();
            if (check > 0) return true;
            return false;

        }


        public Boolean Update(Category cat)
        {

            SqlConnection con = new SqlConnection();
            con.ConnectionString = JamesThew.Includes.Core.GetConnectionString();
            con.Open();
            SqlCommand cmd = new SqlCommand("UpdateCategory", con);
            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.Add(new SqlParameter("@CategoryID", cat.CategoryID));
            cmd.Parameters.Add(new SqlParameter("@CategoryName", cat.CategoryName));
            cmd.Parameters.Add(new SqlParameter("@CategoryDescription", cat.CategoryDescription));
            cmd.Parameters.Add(new SqlParameter("@CategoryParent", cat.CategoryParent));
            cmd.Parameters.Add(new SqlParameter("@PostCount", cat.PostCount));


            Int32 check = cmd.ExecuteNonQuery();
            con.Close();
            if (check > 0) return true;
            return false;

        }

    }
}